﻿using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;

namespace Data
{
    public class DataManager
    {
        Database database = null;


        public DataManager()
        {
            database = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);
        }

        public DataManager(string ConnectionString)
        {
            database = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(ConnectionString);
        }

        public DataTable ExecuteDataTable(string storedProcedureName, params object[] parameterValues)
        {
            using (DbConnection sqlConnection = database.CreateConnection())
            {
                using (DbCommand sqlCommand = database.GetStoredProcCommand(storedProcedureName, parameterValues))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandTimeout = 0;
                    sqlCommand.Connection = sqlConnection;

                    DataTable dt = new DataTable("Table");
                    using (DbDataAdapter sqlda = database.GetDataAdapter())
                    {
                        sqlda.SelectCommand = sqlCommand;
                        dt.BeginLoadData();
                        sqlda.Fill(dt);
                    }
                    dt.EndLoadData();
                    return dt;
                }
            }
        }

        public DataTable ExecuteDataTableByQuery(string tsql)
        {
            using (DbConnection sqlConnection = database.CreateConnection())
            {
                using (DbCommand sqlCommand = database.GetSqlStringCommand(tsql))
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandTimeout = 0;
                    sqlCommand.Connection = sqlConnection;

                    DataTable dt = new DataTable("Table");
                    using (DbDataAdapter sqlda = database.GetDataAdapter())
                    {
                        sqlda.SelectCommand = sqlCommand;
                        dt.BeginLoadData();
                        sqlda.Fill(dt);
                    }
                    dt.EndLoadData();
                    return dt;
                }
            }
        }

        public DataSet ExecuteDataSet(string storedProcedureName, params object[] parameterValues)
        {
            using (DbConnection sqlConnection = database.CreateConnection())
            {
                using (DbCommand sqlCommand = database.GetStoredProcCommand(storedProcedureName, parameterValues))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandTimeout = 0;
                    sqlCommand.Connection = sqlConnection;

                    DataSet ds = new DataSet("DataSet");
                    using (DbDataAdapter sqlda = database.GetDataAdapter())
                    {
                        sqlda.SelectCommand = sqlCommand;
                        sqlda.Fill(ds);
                    }
                    return ds;
                }
            }
        }

        public object ExecuteScaler(string storedProcedureName, params object[] parameterValues)
        {
            using (DbConnection sqlConnection = database.CreateConnection())
            {
                using (DbCommand sqlCommand = database.GetStoredProcCommand(storedProcedureName, parameterValues))
                {
                    sqlConnection.Open();
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandTimeout = 0;
                    sqlCommand.Connection = sqlConnection;
                    return sqlCommand.ExecuteScalar();
                }
            }
        }

        public int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)
        {
            using (DbConnection sqlConnection = database.CreateConnection())
            {
                using (DbCommand sqlCommand = database.GetStoredProcCommand(storedProcedureName, parameterValues))
                {
                    sqlConnection.Open();
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandTimeout = 0;
                    sqlCommand.Connection = sqlConnection;

                    return sqlCommand.ExecuteNonQuery();
                }
            }

        }
    }

}